<?php
	class query_list_bigiot_xyz {
		// 插入扇贝物联转发数据记录
		const query_append_record =
			"INSERT INTO mqtt_drr_bigiot_xyz (
				client_id,
				topic,
				username,
				payload_key,
				payload_value,
				peername,
				`timestamp`
			)
			VALUES
				(
					?,
					?,
					?,
					?,
					?,
					?,
					?
				)";
		
		const query_echarts_count =
			"SELECT
				COUNT(client_id) as counter
			FROM
				mqtt_drr_bigiot_xyz
			WHERE
				FROM_UNIXTIME(ROUND(`timestamp` / 1000, 0)) > DATE_SUB(NOW(),
				INTERVAL 24 HOUR)
			GROUP BY
				client_id
			HAVING
				counter > 1
			ORDER BY
				client_id;";

		// const query_echarts_legend =
		// 	"SELECT
		// 		client_id
		// 	FROM
		// 		mqtt_drr_bigiot_xyz
		// 	GROUP BY
		// 		client_id;";

		const query_echarts_title =
			"SELECT
				client_id
			FROM
				mqtt_drr_bigiot_xyz
			WHERE
				FROM_UNIXTIME(ROUND(`timestamp` / 1000, 0)) > DATE_SUB(NOW(),
				INTERVAL 24 HOUR)
			GROUP BY
				client_id
			HAVING
				COUNT(client_id) > 1
			ORDER BY
				client_id;";
		
		const query_echarts_series_data =
			"SELECT
				payload_value,
				FROM_UNIXTIME(ROUND(`timestamp` / 1000, 0), '%H:%i')
			FROM
				mqtt_drr_bigiot_xyz
			WHERE
				client_id = ?
			AND
				FROM_UNIXTIME(ROUND(`timestamp` / 1000, 0)) > DATE_SUB(NOW(),INTERVAL 24 HOUR);";
		
		const query_first_recorder =
			"SELECT
				ROUND(`timestamp` / 1000, 0)
			FROM
				mqtt_drr_bigiot_xyz
			ORDER BY
				id ASC
			LIMIT 1";

		const query_clean_outdate_data = 
			"DELETE
			FROM
				mqtt_drr_bigiot_xyz
			WHERE
				`timestamp` < ?";
	}
?>